﻿using System;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//using System.Windows.Forms;
using org.in2bits.MyXls;

namespace Utils
{
    public class ExcelUtility
    {
        [STAThreadAttribute]

        #region GridView导出为Excel
        public static void PrepareControlForExport(System.Web.UI.Control control)
        {
            for (int i = 0; i < control.Controls.Count; i++)
            {
                System.Web.UI.Control current = control.Controls[i];
                if (current is LinkButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                }
                else if (current is ImageButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                }
                else if (current is HyperLink)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                }
                else if (current is DropDownList)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                }
                else if (current is System.Web.UI.WebControls.CheckBox)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as System.Web.UI.WebControls.CheckBox).Checked ? "True" : "False"));
                }

                if (current.HasControls())
                {
                    PrepareControlForExport(current);
                }
            }
        }

        public static void GridViewToExcel(System.Web.UI.Control ctl, string FileName)
        {
           // PrepareControlForExport(ctl);
            HttpContext.Current.Response.Charset = "GB2312";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF7;


            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString());
            HttpContext.Current.Response.ContentType = "application/ms-excel";

            ctl.Page.EnableViewState = false;
            System.IO.StringWriter tw = new System.IO.StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            ctl.RenderControl(hw);
            HttpContext.Current.Response.Write(tw.ToString());
            HttpContext.Current.Response.End();
        }

        #endregion
        public static void ExportDsToXls(Page page, DataTable table, string fileName)
        {
            string xlsName = System.Web.HttpContext.Current.Server.MapPath("~/log/" + fileName);
            if (System.IO.File.Exists(xlsName)) System.IO.File.Delete(xlsName);

            XlsDocument xls = new XlsDocument();
            xls.FileName = xlsName;

            int rowIndex = 1;
            int colIndex = 0;


            //DataTable table = ds.Tables[0];
            Worksheet sheet = xls.Workbook.Worksheets.Add("导出数据");//状态栏标题名称
            Cells cells = sheet.Cells;
            foreach (DataColumn col in table.Columns)
            {
                colIndex++;
                //sheet.Cells.AddValueCell(1,colIndex,col.ColumnName);//添加XLS标题行
                cells.Add(1, colIndex, col.ColumnName);

            }
            Cell cell;
            foreach (DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    //sheet.Cells.AddValueCell(rowIndex, colIndex, row[col.ColumnName].ToString());//将数据添加到xls表格里
                    //Cell cell= cells.AddValueCell(rowIndex, colIndex, Convert.ToDouble(row[col.ColumnName].ToString()));//转换为数字型
                    cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString());
                    //如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
                    //cell.Font.FontFamily = FontFamilies.Roman; //字体
                    //cell.Font.Bold = true; //字体为粗体 
                    //cell.Font.Weight = FontWeight.Bold;

                }
            }
            xls.Save();

            ReadFile(page, xlsName);
            page.Response.End();//结束

        }

        public static void ExportDsToXls(Page page, DataSet ds, string fileName)
        {
            string xlsName = System.Web.HttpContext.Current.Server.MapPath("~/log/" + fileName);
            if (System.IO.File.Exists(xlsName)) System.IO.File.Delete(xlsName);

            XlsDocument xls = new XlsDocument();
            xls.FileName = xlsName;

            int rowIndex = 1;
            int colIndex = 0;


            DataTable table = ds.Tables[0];
            Worksheet sheet = xls.Workbook.Worksheets.Add("导出数据");//状态栏标题名称
            Cells cells = sheet.Cells;
            foreach (DataColumn col in table.Columns)
            {
                colIndex++;
                //sheet.Cells.AddValueCell(1,colIndex,col.ColumnName);//添加XLS标题行
                cells.Add(1, colIndex, col.ColumnName);

            }
            Cell cell;
            foreach (DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    //sheet.Cells.AddValueCell(rowIndex, colIndex, row[col.ColumnName].ToString());//将数据添加到xls表格里
                    //Cell cell= cells.AddValueCell(rowIndex, colIndex, Convert.ToDouble(row[col.ColumnName].ToString()));//转换为数字型
                    cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString());
                    //如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
                    //cell.Font.FontFamily = FontFamilies.Roman; //字体
                    //cell.Font.Bold = true; //字体为粗体 
                    //cell.Font.Weight = FontWeight.Bold;

                }
            }
            xls.Save();

            ReadFile(page, xlsName);
            page.Response.End();//结束

        }
        //导出工资条
        public static void ExportToXls(Page page, DataTable table)
        {
            string xlsName = System.Web.HttpContext.Current.Server.MapPath("~/log/SalaryTableList.xls");
            if (System.IO.File.Exists(xlsName)) System.IO.File.Delete(xlsName);

            XlsDocument xls = new XlsDocument();
            xls.FileName = xlsName;

            int rowIndex = 1;
            int colIndex = 0;

            Worksheet sheet = xls.Workbook.Worksheets.Add("导出工资条");//状态栏标题名称
            Cells cells = sheet.Cells;
            //foreach (DataColumn col in table.Columns)
            //{
            //    colIndex++;
            //    //sheet.Cells.AddValueCell(1,colIndex,col.ColumnName);//添加XLS标题行
            //    cells.Add(1, colIndex, col.ColumnName);

            //}
            Cell cell;
            foreach (DataRow row in table.Rows)
            {
                rowIndex++;
                int colIndex2 = 0;
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    colIndex2++;
                    cells.Add(rowIndex, colIndex2, table.Columns[i].ColumnName);
                }

                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    //sheet.Cells.AddValueCell(rowIndex, colIndex, row[col.ColumnName].ToString());//将数据添加到xls表格里
                    //Cell cell= cells.AddValueCell(rowIndex, colIndex, Convert.ToDouble(row[col.ColumnName].ToString()));//转换为数字型
                    cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString());

                    //如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。
                    //cell.Font.FontFamily = FontFamilies.Roman; //字体
                    //cell.Font.Bold = true; //字体为粗体 
                    //cell.Font.Weight = FontWeight.Bold;

                }

            }
            xls.Save();

            ReadFile(page, xlsName);
            page.Response.End();//结束

        }
        //发送文件到客户端
        public static void ReadFile(Page page, string destFileName)
        {
            if (File.Exists(destFileName))
            {
                FileInfo fi = new FileInfo(destFileName);
                page.Response.Clear();
                page.Response.ClearHeaders();
                page.Response.Buffer = false;

                page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Path.GetFileName(destFileName), System.Text.Encoding.UTF8));
                page.Response.AppendHeader("Content-Length", fi.Length.ToString());
                page.Response.ContentType = "application/vnd.ms-excel";//定义输出流类型为 excel 流。关键语句。
                page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                page.Response.WriteFile(destFileName);
                page.Response.Flush();
                page.Response.End();
            }
            else
            {
                page.Response.Write("<script langauge=javascript>alert('文件不存在!');history.go(-1);</script>");
                page.Response.End();
            }
        }



        //       汉语名称问题 终于找到解决方法，现记载如下：

        //1Response.Clear();
        //2   Response.Buffer = true;
        //3   Response.Charset = "gb2312";
        //4   Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        //5   Response.AppendHeader("content-disposition","attachment;filename=\"" + System.Web.HttpUtility.UrlEncode("中文名称",System.Text.Encoding.UTF8) + ".xls\"");
        //6   Response.ContentType = "Application/ms-excel";3、身份证号码的问题：存储为字符串
        //        if (e.Row.RowType == DataControlRowType.DataRow)
        //        {
        //            e.Row.Cells[2].Attributes.Add("style", "vnd.ms-excel.numberformat:@");//身份证号列
        //            e.Item.Cells[3].Attributes.Add("style","vnd.ms-excel.numberformat:￥#,###.00");//金额
        //        } 
        //导入住宿费用
        public static bool ImportEmpFei(string strExtension, string servePath)
        {
            bool flag = true;
            try
            {
                string strExcelConn = "";
                if (strExtension == ".xls")// Excel 97-2003
                    strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + servePath + ";Extended Properties='Excel 8.0;HDR=YES;'";
                else// Excel 2007
                    strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + servePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";

                OleDbConnection thisconnection = new OleDbConnection(strExcelConn);
                thisconnection.Open();
                string Sql = "select 工号,年,月,金额 from [Sheet1$] where 工号<>''";
                OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
                DataSet ds = new DataSet();
                mycommand.Fill(ds, "[Sheet1$]");
                thisconnection.Close();

                string conString = XmlUtility.GetConnFormConfig("JIMConn");
                using (SqlConnection conn = new SqlConnection(conString))
                {
                    conn.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conString))
                    {
                        bulkCopy.DestinationTableName = "Mini_HostelEmpFee";
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("工号", "EmpCode"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("年", "FYear"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("月", "FMonth"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("金额", "Fei"));                      
                        bulkCopy.BatchSize = 300;
                        bulkCopy.WriteToServer(ds.Tables["[Sheet1$]"]);
                    }
                }
            }
            catch
            { flag = false; }
            return flag;
        }
        //导入保险信息
        public static bool ImportInsurance(string strExtension, string servePath)
        {
            bool flag = true;
            try
            {
                string strExcelConn = "";
                if (strExtension == ".xls")// Excel 97-2003
                    strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + servePath + ";Extended Properties='Excel 8.0;HDR=YES;'";
                else// Excel 2007
                    strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + servePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";

                OleDbConnection thisconnection = new OleDbConnection(strExcelConn);
                thisconnection.Open();
                string Sql = "select 工号,单号,周期,类别,基数,个人金额,公司金额,开始时间 from [Sheet1$] where 工号<>''";
                OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
                DataSet ds = new DataSet();
                mycommand.Fill(ds, "[Sheet1$]");
                thisconnection.Close();

                string conString = XmlUtility.GetConnFormConfig("JIMConn");
                using (SqlConnection conn = new SqlConnection(conString))
                {
                    conn.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conString))
                    {
                        bulkCopy.DestinationTableName = "Admin_Insurance";
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("工号", "EmpCode"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("单号", "InsuranceNo"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("周期", "InsurancePeriod"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("基数", "BaseValue"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("个人金额", "EmpPay"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("公司金额", "ComPay"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("类别", "TypeID"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("开始时间", "BeginTime"));
                        bulkCopy.BatchSize = 1000;
                        bulkCopy.WriteToServer(ds.Tables["[Sheet1$]"]);
                    }
                }
            }
            catch
            { flag = false; }
            return flag;
        }

        //导入工资基本工资信息
        public static bool ImportEmpItem(string strExtension, string servePath, Hashtable ht, string itType, string include)
        {
            bool flag = true;
            try
            {
                string strExcelConn = "";
                if (strExtension == ".xls")// Excel 97-2003
                {
                    strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + servePath + ";Extended Properties='Excel 8.0;HDR=YES;'";
                }
                else// Excel 2007
                {
                    strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + servePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                }

                OleDbConnection thisconnection = new OleDbConnection(strExcelConn);
                thisconnection.Open();

                string fields = "";
                foreach (System.Collections.DictionaryEntry objDE in ht)
                {
                    fields += "," + objDE.Value.ToString();
                }
                //
                 string Sql = "select 工号 " + fields + " from [Sheet1$] where 工号<>''";
                OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
                DataSet ds = new DataSet();
                mycommand.Fill(ds, "[Sheet1$]");
                thisconnection.Close();
                string conString = XmlUtility.GetConnFormConfig("JIMConn");
                //删除已经存在的
                if (itType == "Update")
                {
                    StringBuilder builder = new StringBuilder("if   exists(select   1   from   sysobjects   where   name= 'Wage_EmpItemBk'   and   xtype= 'U ')  begin drop table Wage_EmpItemBk end; select * into Wage_EmpItemBk from Wage_EmpItem;");
                    for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
                    {
                        builder.Append("insert into Wage_EmpItemSwap select * from Wage_EmpItem Where EmpCode='" + ds.Tables[0].Rows[row]["工号"].ToString().Trim() + "';Delete from Wage_EmpItem Where EmpCode='" + ds.Tables[0].Rows[row]["工号"].ToString().Trim() + "';");
                    }
                    using (SqlConnection conn = new SqlConnection(conString))
                    {
                        SqlCommand comm = new SqlCommand();
                        comm.Connection = conn;
                        conn.Open();

                        comm.CommandType = CommandType.Text;
                        comm.CommandText = builder.ToString();
                        comm.CommandTimeout = 120;
                        comm.ExecuteNonQuery();
                        comm.Dispose();
                    }
                }
                ////删除重复项
                StringBuilder builder2 = new StringBuilder();
                for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
                {
                    builder2.Append("Delete from Wage_EmpItem Where EmpCode='" + ds.Tables[0].Rows[row]["工号"].ToString().Trim() + "';");
                }
                 using (SqlConnection conn = new SqlConnection(conString))
                    {
                        SqlCommand comm = new SqlCommand();
                        comm.Connection = conn;
                        conn.Open();

                        comm.CommandType = CommandType.Text;
                        comm.CommandText = builder2.ToString();
                        comm.CommandTimeout = 120;
                        comm.ExecuteNonQuery();
                        comm.Dispose();
                    }
            
                //插入数据
                using (SqlConnection conn = new SqlConnection(conString))
                {
                    conn.Open();
                 
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conString))
                    {
                        bulkCopy.DestinationTableName = "Wage_EmpItem";
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("工号", "EmpCode"));
                        if (include == "YES")
                        {
                             bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("银行名称", "BankName"));
                              bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("银行账号", "BankAccount"));
                        }
                        foreach (System.Collections.DictionaryEntry objDE in ht)
                        {
                            string value = objDE.Value.ToString();
                            //if (string.IsNullOrWhiteSpace(value)) value = "0";
                            string key = objDE.Key.ToString();
                            if (string.IsNullOrWhiteSpace(key)) continue;
                            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(value, key));
                        }

                        bulkCopy.BatchSize = 1000;
                        bulkCopy.WriteToServer(ds.Tables["[Sheet1$]"]);
                    }
                }
            }
            catch (Exception  ee)
            {
                LogHelper.WriteLog(ee.Message);
                
                flag = false; }
            return flag;
        }
        //导入短款信息
        public static bool ImportShortage(string strExtension, string servePath, string account)
        {
            bool flag = true;
            try
            {
                string strExcelConn = "";
                if (strExtension == ".xls")// Excel 97-2003
                    strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + servePath + ";Extended Properties='Excel 8.0;HDR=YES;'";
                else// Excel 2007
                    strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + servePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";

                OleDbConnection thisconnection = new OleDbConnection(strExcelConn);
                thisconnection.Open();
                string Sql = "select 工号,短款时间,金额,原因 from [Sheet1$]  where 工号<>'' ";
                OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
                DataSet ds = new DataSet();
                mycommand.Fill(ds, "[Sheet1$]");
                thisconnection.Close();

                string conString = XmlUtility.GetConnFormConfig("JIMConn");
                using (SqlConnection conn = new SqlConnection(conString))
                {
                    conn.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conString))
                    {
                        bulkCopy.DestinationTableName = "Wage_Shortage";
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("工号", "EmpCode"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("短款时间", "RewardsTime"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("金额", "RewardsAmount"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("原因", "RewardsReason"));                        
                        bulkCopy.BatchSize = 500;
                        bulkCopy.WriteToServer(ds.Tables["[Sheet1$]"]);
                    }
                }
                //更新---不全员工信息
                string strSQL = "update a set a.EmpName=b.EmpName,a.DeptName=b.DeptName,a.DutyName=b.DutyName,a.StateName=b.StateName,a.Account='" + account + "' from Wage_Shortage a,View_Emp b where a.EmpCode=b.EmpCode and isnull(a.EmpName,'')=''";
                using (SqlConnection conn = new SqlConnection(conString))
                {
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = strSQL;
                    comm.CommandTimeout = 120;
                    comm.ExecuteNonQuery();
                    comm.Dispose();
                }

            }
            catch
            { flag = false; }
            return flag;
        }


    }
}
